// sql语句
var mysql = {
    // 用户管理
    user: {
        //登录验证语句
        login: [
            "select * from v_admin where v_name = ? and v_passwd = ?",//公司
            "select * from v_institution_user where v_name = ? and v_passwd = ?",//机构
            "select * from v_kefu_user where v_name = ? and v_passwd = ?"//客服
        ],
        //指定用户查询语句
        login2: [
            "select * from v_admin where v_name = ?",//公司
            "select * from v_institution_user where v_name = ?",//机构
            "select * from v_kefu_user where v_name = ?"//客服
        ],
        //令牌登录更新语句
        token: [
            "UPDATE v_admin SET v_token = ?,v_time_old = ? WHERE v_name = ?",//公司
            "UPDATE v_institution_user SET v_token = ?,v_time_old = ? WHERE v_name = ?",//机构
            "UPDATE v_kefu_user SET v_token = ?,v_time_old = ? WHERE v_name = ?"//客服
        ],
        //令牌更新清除语句
        token2: [
            "UPDATE v_admin SET v_token = ? WHERE v_token = ?",//公司
            "UPDATE v_institution_user SET v_token = ? WHERE v_token = ?",//机构
            "UPDATE v_kefu_user SET v_token = ? WHERE v_token = ?"//客服
        ],
        //令牌验证语句
        token3: {
            check: [
                "select * from v_admin where v_name = ? and v_token = ?",//公司
                "select * from v_institution_user where v_name = ? and v_token = ?",//机构
                "select * from v_kefu_user where v_name = ? and v_token = ?"//客服
            ],
        },
        //客服语句
        kefu: {
            //客服查询语句
            check: [
                "select v_id as id,v_name as name,v_title as title,v_form as form,DATE_FORMAT(v_time_old, '%Y-%m-%d %H:%i:%S') as timeold,v_state as state from v_kefu_user where v_form = ?",//客服列表
                "select v_id as id,v_name as name,v_title as title,v_form as form,DATE_FORMAT(v_time_old, '%Y-%m-%d %H:%i:%S') as timeold,v_state as state from v_kefu_user"//超级管理员客服列表
            ],
            //客服删除语句
            del: [
                "DELETE FROM v_kefu_user WHERE v_id IN (?)",//客服账号删除语句
            ],
            //客服更新语句
            mod: [
                "UPDATE v_kefu_user SET v_name = ?,v_passwd = ?,v_title=?,v_form = ? WHERE v_id = ?",
                "UPDATE v_kefu_user SET v_name = ? , v_title = ? , v_form = ? WHERE v_id = ?",
            ]
        },
        //管理员语句
        admin: {
            //管理员查询语句
            check: [
                "select v_id as id,v_name as name,'公司' as form,DATE_FORMAT(v_time_old, '%Y-%m-%d %H:%i:%S') as timeold,v_title as title,'0' as role from v_admin where v_power >= ?",//公司管理员列表
                "select * from v_admin where v_id = ?",
            ],
            del: [
                "DELETE FROM v_admin WHERE v_id IN (?)",//管理员账号删除语句
            ],
            add: [
                "INSERT INTO v_admin (v_name,v_title,v_passwd,v_power) VALUES (?,?,?,?); "
            ],
            mod: [
                "UPDATE v_admin SET v_name = ?,v_passwd = ?,v_title=? WHERE v_id = ?",
                "UPDATE v_admin SET v_name = ? , v_title = ? WHERE v_id = ?",
            ]
        },
        //组织语句
        user: {
            //组织列表查询语句
            check: [
                "select v_id as id,v_name as name,v_form as form,DATE_FORMAT(v_time_old, '%Y-%m-%d %H:%i:%S') as timeold,v_title as title,'1' as role from v_institution_user",//机构管理员列表
                "select * from v_institution_user where v_id = ?",
                "select * from v_institution_user where v_name = ?",//查询机构管理员账号
            ],
            del: [
                "DELETE FROM v_institution_user WHERE v_id IN (?)",//机构账号删除语句
            ],
            mod: [
                "UPDATE v_institution_user SET v_name = ?,v_passwd = ?,v_title=? WHERE v_id = ?",
                "UPDATE v_institution_user SET v_name = ? , v_title = ? WHERE v_id = ?",
            ]
        },
        //机构
        inst: {
            list: [
                "select v2_id as id,v2_title as title, v2_pname as pname,v2_phone as phone,v2_website as website,v2_itype as itype,v2_item as item,v2_fund as fund,DATE_FORMAT(v2_time, '%Y-%m-%d %H:%i:%S') as time from v_institution where v2_examine = 1"
            ],
            add: [
                "INSERT INTO v_institution (v2_title,v2_pname,v2_phone,v2_website,v2_itype,v2_item,v2_fund) VALUES (?,?,?,?,?,?,?)",
            ],
            check: [
                "select * from v_institution where v2_title = ?",
            ],
            mod: [
                "UPDATE v_institution SET v2_title = ?,v2_pname = ?,v2_phone=?,v2_website=?,v2_itype=?,v2_item=?,v2_fund=? WHERE v2_id = ?",
            ],
            examine: [
                "select v2_id as id,v2_title as title, v2_pname as pname,v2_phone as phone,v2_website as website,v2_itype as itype,v2_item as item,v2_fund as fund,v2_examine as examine,DATE_FORMAT(v2_time, '%Y-%m-%d %H:%i:%S') as time from v_institution where v2_examine = ?",//0未审核 1已审核 2驳回
                "UPDATE v_institution SET v2_examine = ? WHERE v2_id = ?",//审核
            ],
            key: [
                "select * from v_key where v3_form = ?",//获取机构KEY
                "select * from v_key where v3_key = ?",//获取机构KEY2
                "UPDATE v_key SET v3_key = ?,v3_name = ? WHERE v3_form = ?",//更新机构key
                "INSERT INTO v_key (v3_form,v3_key,v3_name) VALUES (?,?,?)",//添加机构key
            ]
        },
        //机构列表
        info: {
            role: [
                "select v2_id as id,v2_title as title from v_institution where v2_examine = 1",//超级管理员机构列表0
                "select v2_id as id,v2_title as title from v_institution where v2_title = ?",//机构列表
            ],
            power: [
                "select v0_sign as id,v0_title as title from v_power",//超级管理员机构列表
                "select v0_sign as id,v0_title as title from v_power where v0_sign >= ?",//机构列表
            ],
        },
        //添加人员
        adduser: [
            "INSERT INTO v_admin (v_name,v_title,v_passwd,v_power) VALUES (?,?,?,?)",
            "INSERT INTO v_institution_user (v_name,v_title,v_passwd,v_form,v_power) VALUES (?,?,?,?,?)",
            "INSERT INTO v_kefu_user (v_name,v_title,v_passwd,v_form,v_power) VALUES (?,?,?,?,?)"
        ],
        //修改密码
        passwd: [
            "UPDATE v_admin SET v_passwd = ? WHERE v_name = ?",
            "UPDATE v_institution_user SET v_passwd = ? WHERE v_name = ?",
            "UPDATE v_kefu_user SET v_passwd = ? WHERE v_name = ?",
        ],
        sys: {
            add: [
                "INSERT INTO v_sys_log (v1_uid,v1_name,v1_title,v1_desc,v1_state,v1_type) VALUES (?,?,?,?,?,?);",//日志记录
            ],
            check: [
                "SELECT v1_id AS id,v1_uid AS uid,v1_name AS `name`,v1_title AS title,v1_desc AS `desc`,v1_state AS state,v1_type AS `type`,DATE_FORMAT(v1_time, '%Y-%m-%d %H:%i:%S') as time FROM v_sys_log WHERE v1_name = ? AND v1_uid = ? ORDER BY v1_id DESC",//只获取使用者记录
                "SELECT v1_id AS id,v1_uid as uid,v1_name AS `name`,v1_title AS `title`,v1_desc AS `desc`,v1_state AS state,v1_type AS `type`,DATE_FORMAT(v1_time, '%Y-%m-%d %H:%i:%S') as time FROM v_sys_log ORDER BY v1_id DESC",//获取所有使用者记录
            ]
        },
        knowledge: {
            add: [
                "INSERT INTO v_knowledge (v6_form,v6_key,v6_content,v6_type,v6_executor) VALUES (?,?,?,?,?);",//添加知识库数据
            ],
            check: [
                "select v6_id as `id`,v6_form as `form`,v6_key as `key`,v6_content as `content`,v6_type as `type`,v6_executor as `executor`,DATE_FORMAT(v6_time, '%Y-%m-%d %H:%i:%S') as `time` from v_knowledge",//公司管理员获取知识库列表
                "select v6_id as `id`,v6_form as `form`,v6_key as `key`,v6_content as `content`,v6_type as `type`,v6_executor as `executor`,DATE_FORMAT(v6_time, '%Y-%m-%d %H:%i:%S') as `time` from v_knowledge where v6_form = ?",//机构管理员获取知识库列表
            ],
            del: [
                "DELETE FROM v_knowledge WHERE v6_id IN (?)",//知识库删除语句
            ],
            mod: [
                "UPDATE v_knowledge SET v6_key = ?,v6_content = ?,v6_type = ?,v6_executor = ? WHERE v6_id = ?",//知识库修改
            ]
        }

    }
}

module.exports = mysql;